package org.demo;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelTest {
	private static String path = "E:/garca/mission/qing/eclipse64/workspace/qc-parent/qc-test/files";
	private static String baseFile = "base.xlsx";
	private static String inFile = "五项精品应用-后台数据.xlsx";
	private static String outFile = "out.xlsx";
	private static Set<String> apps = new HashSet<String>();

	public static void main(String[] args) throws Exception {
		// TODO Auto-generated method stub
		readXlsx(new FileInputStream(new File(path, inFile)));
		updateXslx(new FileInputStream(new File(path, baseFile)), new FileOutputStream(new File(path, outFile)));

	}

	public static void updateXslx(InputStream is, OutputStream output) throws IOException {
		XSSFWorkbook wb = new XSSFWorkbook(is);
		XSSFCellStyle css=wb.createCellStyle();
		DataFormat  format = wb.createDataFormat();
		css.setDataFormat(format.getFormat("@"));
		for (String key : new String[] { "掌上公交", "办事指南", "预约挂号", "城市账单", "车主服务" }) {
			XSSFSheet hssfSheet = wb.getSheet(key);
			int minRow = 3;// 第4行开始
			hssfSheet.setDefaultColumnStyle('R'-'A', css);
			// 循环行Row
			for (int rowNum = minRow; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
				XSSFRow hssfRow = hssfSheet.getRow(rowNum);
				if (hssfRow == null) {
					break;
				}
				getCellString(hssfRow, 'B');
				getCellString(hssfRow, 'C');
				setCellString(hssfRow,'R',"");
				setCellString(hssfRow,'S',"");
				setCellString(hssfRow,'T',"");
				setCellString(hssfRow,'U',"");
			}
		}
		wb.write(output);
		output.flush();
		output.close();

	}

	public static void readXlsx(InputStream is) throws Exception {
		XSSFWorkbook hssfWorkbook = new XSSFWorkbook(is);
		for (String key : new String[] { "掌上公交", "办事指南", "预约挂号", "城市账单", "车主服务" }) {
			XSSFSheet hssfSheet = hssfWorkbook.getSheet(key);
			int minRow = 1;// 第二行开始
			// 循环行Row
			for (int rowNum = minRow; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
				XSSFRow hssfRow = hssfSheet.getRow(rowNum);
				getCellString(hssfRow, 'A');
				
			}
		}
		return;
	}

	/**
	 * 获取单元数据
	 * 
	 * @param hssfRow
	 * @param index
	 * @return
	 */
	public static String getCellString(XSSFRow hssfRow, char index) {
		XSSFCell xh = hssfRow.getCell(index - 'A');
		
		if (xh == null) {
			return null;
		}
		switch (xh.getCellType()) {
		case XSSFCell.CELL_TYPE_STRING:
			return xh.getStringCellValue();
		case XSSFCell.CELL_TYPE_NUMERIC: {
			String fm = xh.getCellStyle().getDataFormatString();
			if (fm != null && fm.contains("yyyy")) {
				java.text.DateFormat dfm = new java.text.SimpleDateFormat("yyyy-MM-dd");
				return dfm.format(xh.getDateCellValue());
			} else {
				return new BigDecimal(xh.getNumericCellValue()).toPlainString();
			}
		}
		case XSSFCell.CELL_TYPE_BOOLEAN:
			return "" + xh.getBooleanCellValue();
		case XSSFCell.CELL_TYPE_FORMULA:
			return "" + xh.getCellFormula();
		case XSSFCell.CELL_TYPE_BLANK:
			return "";
		case XSSFCell.CELL_TYPE_ERROR:
			return "" + xh.getErrorCellString();
		default:
			return null;
		}
	}

	/**
	 * 单元格赋值
	 * 
	 * @param hssfRow
	 * @param index
	 * @param val
	 */
	public static void setCellString(XSSFRow hssfRow, char index, String val) {
		XSSFCell cell = hssfRow.getCell(index - 'A');
		if (cell == null) {

		} else {
			if(isNum(val)){
				cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
				if(isIntValue(val)){
					cell.setCellValue(Integer.valueOf(val));
				}else{
					cell.setCellValue(Double.valueOf(val));
				}
			}else{
				cell.setCellValue(val);
				cell.setCellType(XSSFCell.CELL_TYPE_STRING);
				//cell.setCellStyle(new XSSF);
			}
			
			
			
		}

	}
	
	public static void setFont(XSSFRow hssfRow, char index, String val){
		XSSFFont font=hssfRow.getSheet().getWorkbook().createFont();
		font.setColor(XSSFFont.COLOR_RED);
		XSSFRichTextString dd=new XSSFRichTextString(val);
		dd.applyFont(font);
	}
	/**
	 * 复制单元格
	 * 
	 * @param srcCell
	 * @param distCell
	 * @param copyValueFlag
	 *            true则连同cell的内容一起复制
	 */
	public static void copyCell(XSSFWorkbook wb,XSSFCell srcCell, XSSFCell distCell,
			boolean copyValueFlag) {
		XSSFCellStyle newstyle=wb.createCellStyle();
		copyCellStyle(srcCell.getCellStyle(), newstyle);
		//样式
		distCell.setCellStyle(newstyle);
		//评论
		if (srcCell.getCellComment() != null) {
			distCell.setCellComment(srcCell.getCellComment());
		}
		// 不同数据类型处理
		int srcCellType = srcCell.getCellType();
		distCell.setCellType(srcCellType);
		if (copyValueFlag) {
			if (srcCellType == XSSFCell.CELL_TYPE_NUMERIC) {
				if (isCellDateFormatted(srcCell)) {
					distCell.setCellValue(srcCell.getDateCellValue());
				} else {
					distCell.setCellValue(srcCell.getNumericCellValue());
				}
			} else if (srcCellType == XSSFCell.CELL_TYPE_STRING) {
				distCell.setCellValue(srcCell.getRichStringCellValue());
			} else if (srcCellType == XSSFCell.CELL_TYPE_BLANK) {
				// nothing21
			} else if (srcCellType == XSSFCell.CELL_TYPE_BOOLEAN) {
				distCell.setCellValue(srcCell.getBooleanCellValue());
			} else if (srcCellType == XSSFCell.CELL_TYPE_ERROR) {
				distCell.setCellErrorValue(srcCell.getErrorCellValue());
			} else if (srcCellType == XSSFCell.CELL_TYPE_FORMULA) {
				distCell.setCellFormula(srcCell.getCellFormula());
			} else { // nothing29
			}
		}
	}
	
	public static boolean isCellDateFormatted(XSSFCell cell){
		return true;
	}
	/**
	 * 复制一个单元格样式到目的单元格样式
	 * @param fromStyle
	 * @param toStyle
	 */
	public static void copyCellStyle(XSSFCellStyle fromStyle,
			XSSFCellStyle toStyle) {
		toStyle.setAlignment(fromStyle.getAlignment());
		//边框和边框颜色
		toStyle.setBorderBottom(fromStyle.getBorderBottom());
		toStyle.setBorderLeft(fromStyle.getBorderLeft());
		toStyle.setBorderRight(fromStyle.getBorderRight());
		toStyle.setBorderTop(fromStyle.getBorderTop());
		toStyle.setTopBorderColor(fromStyle.getTopBorderColor());
		toStyle.setBottomBorderColor(fromStyle.getBottomBorderColor());
		toStyle.setRightBorderColor(fromStyle.getRightBorderColor());
		toStyle.setLeftBorderColor(fromStyle.getLeftBorderColor());
		
		//背景和前景
		toStyle.setFillBackgroundColor(fromStyle.getFillBackgroundColor());
		toStyle.setFillForegroundColor(fromStyle.getFillForegroundColor());
		
		toStyle.setDataFormat(fromStyle.getDataFormat());
		toStyle.setFillPattern(fromStyle.getFillPattern());
//		toStyle.setFont(fromStyle.getFont(null));
		toStyle.setHidden(fromStyle.getHidden());
		toStyle.setIndention(fromStyle.getIndention());//首行缩进
		toStyle.setLocked(fromStyle.getLocked());
		toStyle.setRotation(fromStyle.getRotation());//旋转
		toStyle.setVerticalAlignment(fromStyle.getVerticalAlignment());
		toStyle.setWrapText(fromStyle.getWrapText());
		
	}
	/** 
     * 行复制功能 
     * @param fromRow 
     * @param toRow 
     */  
    public static void copyRow(XSSFWorkbook wb,XSSFRow fromRow,XSSFRow toRow,boolean copyValueFlag){  
        for (Iterator cellIt = fromRow.cellIterator(); cellIt.hasNext();) {  
            XSSFCell tmpCell = (XSSFCell) cellIt.next();  
            XSSFCell newCell = toRow.createCell(tmpCell.getColumnIndex());  
            copyCell(wb,tmpCell, newCell, copyValueFlag);  
        }  
    }
    public static boolean isNum(String value) {
		return value != null && value.matches("\\d+(.\\d+)?");
	}
    /**
	 * 判断字符串是否是整型(包括int,long,short)
	 * 
	 * @param value要判断的字符串
	 * @return
	 */
	public static boolean isIntValue(String value) {
		return value != null && value.matches("\\d+");
	}
}
